package db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import sun.rmi.runtime.Log;
import tcpClient.TCPClient;
import util.Sensor;
import util.Train;
import util.User;
import util.MeasurementLog;

public class DB 
{
	static final String controller = "org.sqlite.JDBC";
	private Connection connection = null;
	
	public void connect() throws ClassNotFoundException, SQLException
	{
		Class.forName(controller);
		connection = DriverManager.getConnection("jdbc:sqlite:db/retrain.sqlite");
		
	}
	public void disconnect() throws SQLException
	{
		connection.close();
	}
	public Sensor getSensor(Integer sensor_id)
	{
		Sensor s = new Sensor();
		try
		{
			Statement statement = this.connection.createStatement(); 
			ResultSet resultSet = statement.executeQuery("SELECT * FROM sensor WHERE sensor_id = " + sensor_id + ";");
			if (resultSet.next())
			{
				s.setId(resultSet.getInt("sensor_id"));
				s.setDescription(resultSet.getString("description"));
				s.setState(resultSet.getBoolean("state"));
			}
			else
			{
				s = null;
			}
		resultSet.close();
	    statement.close();
		}
		catch(SQLException exception)
		{
			  System.out.println("Error in the database: "+ exception.getMessage());
		}
		return s;
	}
	
	public User getUser(String login)
	{
		User user = new User();
		try
		{
			Statement statement = this.connection.createStatement(); 
			ResultSet resultSet = statement.executeQuery("SELECT * FROM user WHERE login = '" + login+ "';");
			if (resultSet.next())
			{
				user.setLogin(resultSet.getString("login"));
				user.setPassword(resultSet.getString("pass"));
			}
			else
			{
				user = null;
			}
		resultSet.close();
	    statement.close();
		}
		catch(SQLException exception)
		{
			  System.out.println("Error in the database: "+ exception.getMessage());
		}
		return user;
	}
	
	public Train getTrain(int id)
	{
		Train train = new Train();
		try
		{
			Statement Statement = this.connection.createStatement(); 
			ResultSet resultSet = Statement.executeQuery("SELECT * FROM train WHERE train_id = " + id+ ";");
			if (resultSet.next())
			{
				train.setGps_status(resultSet.getBoolean("gps_status"));
				train.setId(resultSet.getInt("train_id"));
				train.setTrain_status(resultSet.getString("train_status"));
			}
			else
			{
				train = null;
			}
		resultSet.close();
	    Statement.close();
		}
		catch(SQLException exception)
		{
			  System.out.println("Error in the database: "+ exception.getMessage());
		}
		return train;
	}
	
	public ArrayList<Sensor> getSensorList()
	{
		ArrayList<Sensor> sensorList = new ArrayList<Sensor>();
		try
		{
			Statement Statement = this.connection.createStatement(); 
			ResultSet resultSet = Statement.executeQuery("SELECT * FROM sensor;");
			while (resultSet.next())
			{
				Sensor sensor = new Sensor(resultSet.getInt("sensor_id"),resultSet.getString("description"),resultSet.getBoolean("state"));
				sensorList.add(sensor);
			}
			resultSet.close();
			Statement.close();
		}
		catch(SQLException exception)
		{
			  System.out.println("Error in the database:   "+ exception.getMessage());
		}
		return sensorList;
	}

	
	public ArrayList<MeasurementLog> getHistoryLog(int sensor_id)
	{
		ArrayList<MeasurementLog> measurementLogList = new ArrayList<MeasurementLog>();
		try
		{
			Statement Statement = this.connection.createStatement(); 
			ResultSet resultSet = Statement.executeQuery("SELECT * FROM log WHERE sensor_id = " + sensor_id + ";");
			while (resultSet.next())
			{
				MeasurementLog log = new MeasurementLog(resultSet.getInt("log_id"),resultSet.getInt("sensor_id")
				     ,resultSet.getString("date"),resultSet.getString("time"),resultSet.getString("coordinates"),resultSet.getInt("value"));
				measurementLogList.add(log);
			}
			resultSet.close();
			Statement.close();
		}
		catch(SQLException exception)
		{
			  System.out.println("Error in the database:   "+ exception.getMessage());
		}
		return measurementLogList;
	}
	
	public MeasurementLog getCurrentLog(int sensor_id)
	{
		ArrayList<MeasurementLog> logList = this.getHistoryLog(sensor_id);
		int size = logList.size();
		if(size==0)
		{
			System.out.println("logList=0");
		}
		return logList.get(size-1);
	}
	
	public String getTrainCoordinates()
	{
		String coordinates = null;
		Train train = this.getTrain(1);
		try
		{
			Statement Statement = this.connection.createStatement();
			ResultSet resultSet = Statement.executeQuery("SELECT * FROM train;");
			if(resultSet.next())
			{
				coordinates = resultSet.getString("coordinates");
			}
			Statement.close();
		}
		catch(SQLException exception)
		{
			  System.out.println("Error in the database:   "+ exception.getMessage());
		}
		
		return coordinates;
	}
	
	//VALIDATE DATA
	
	public boolean correctUserLogin(String login)
	{
		boolean correct = false;
		User user = this.getUser(login);
		if ((user != null)&&(user.getLogin().equals(login)))
		{
			correct = true;
		}
		return correct;
	}
	public boolean correctUserPass(String login, String pass)
	{
		boolean correct = false;
		User user = this.getUser(login);
		if ((user != null)&&(user.getPassword().equals(pass)))
		{
			correct = true;
		}
		return correct;
		
	}
	public boolean correctSensorId(int id)
	{
		boolean correct = false;
		Sensor sensor = this.getSensor(id);
		if (sensor != null)
		{
			correct = true;
		}
		return correct;
	}
	
	//CHANGE SENSOR AND GPS STATE
	public void changeSensorState(int id)
	{
		Sensor sensor = this.getSensor(id);
		try
		{
			Statement Statement = this.connection.createStatement(); 
			if (sensor.getState()) //ON
			{
				Statement.executeUpdate("UPDATE sensor SET state = 0 WHERE sensor_id = " + id + ";");
			}
			else					//OFF
			{
				Statement.executeUpdate("UPDATE sensor SET state = 1 WHERE sensor_id = " + id + ";");
			}
			Statement.close();
		}
		catch(SQLException exception)
		{
			  System.out.println("Error in the database:   "+ exception.getMessage());
		}
	}
	
	public void changeGPSState()
	{
		Train train = this.getTrain(1);
		try
		{
			Statement Statement = this.connection.createStatement(); 
			if (train.getGps_status()) //ON
			{
				Statement.executeUpdate("UPDATE train SET gps_status = 0 WHERE train_id = 1;");
			}
			else					//OFF
			{
				Statement.executeUpdate("UPDATE train SET gps_status = 1 WHERE train_id = 1;");
			}
			Statement.close();
		}
		catch(SQLException exception)
		{
			  System.out.println("Error in the database:   "+ exception.getMessage());
		}
	}
	
	//SERVER ADMINISTRATION
	
	public ArrayList<User> getUserList(String login, String pass)
	{
		ArrayList<User> userList = new ArrayList<User>();
		try
		{
			Statement Statement = this.connection.createStatement(); 
			ResultSet resultSet = Statement.executeQuery("SELECT * FROM user;");
			if (resultSet.next())
			{
				User user = new User(resultSet.getString("login"),resultSet.getString("pass"));
				userList.add(user);
			}
			resultSet.close();
			Statement.close();
		}
		catch(SQLException exception)
		{
			  System.out.println("Error in the database:   "+ exception.getMessage());
		}
		return userList;
	}
	
	public void addUser (String login, String pass)
	{
		try
		{
			Statement Statement = this.connection.createStatement(); 
			Statement.executeQuery("INSERT INTO user  VALUES ('" + login + "','" + pass + "');");
			Statement.close();
		}
		catch(SQLException exception)
		{
			  System.out.println("Error in the database:   "+ exception.getMessage());
		}
	}
	
	public void removeUser (String login)
	{
		try
		{
			Statement Statement = this.connection.createStatement(); 
			Statement.executeQuery("DELETE FROM user  WHERE login = '" + login + "';");
			Statement.close();
		}
		catch(SQLException exception)
		{
			  System.out.println("Error in the database:   "+ exception.getMessage());
		}
	}
	
	public void updateUserLogin (String firstLogin,String secondLogin, String pass)
	{
		try
		{
			Statement Statement = this.connection.createStatement(); 
			Statement.executeUpdate("UPDATE user SET login = '"+secondLogin+"', pass = '"+pass
					                               +"' WHERE login = '"+firstLogin+"';");
			Statement.close();
		}
		catch(SQLException exception)
		{
			  System.out.println("Error in the database:   "+ exception.getMessage());
		}
	}
	
	public void updateUserPass (String login, String secondPass)
	{
		try
		{
			Statement Statement = this.connection.createStatement(); 
			Statement.executeQuery("UPDATE user SET login = '"+login+"', pass = '"+secondPass
					                               +"' WHERE login = '"+login+"';");
			Statement.close();
		}
		catch(SQLException exception)
		{
			  System.out.println("Error in the database:   "+ exception.getMessage());
		}
	}
	
	public static void main(String[] args)
	{
		DB db = new DB();
		try {
			db.connect();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		Sensor sensor = db.getSensor(1);
		System.out.println("Sensor 1:" + sensor.getDescription());
		User user = db.getUser("itziar");
		System.out.println("user 1:" + user.getPassword());
		Train train = db.getTrain(1);
		System.out.println("train:" + train.getStatus() + "," + train.getGps());
		ArrayList<Sensor> sensorList = db.getSensorList();
		System.out.println("Sensor list" + sensorList.size());
		for (int i = 0;i < sensorList.size(); i++)
		{
			System.out.println("Sensor" + sensorList.get(i).getDescription());
		}
		
		System.out.println(db.getTrainCoordinates());
		
		ArrayList<MeasurementLog> historyLog = db.getHistoryLog(1);
		System.out.println(historyLog.size());
		for(int i = 0; i<historyLog.size();i++)
		{
			System.out.println(historyLog.get(i).getDate() + ";" + historyLog.get(i).getTime() + ";" + historyLog.get(i).getCoordinates() + ";" + historyLog.get(i).getValue());
		}
		
		
		try {
			db.disconnect();
		} catch (SQLException e) 
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
}